var path = require("path");
var sqlitePath = process.cwd() + path.sep + "data" + path.sep + "system" + path.sep + "ypcp.db";
console.log(sqlitePath);
var sqlite3 = require('sqlite3').verbose();

function log_err(err){
	if(err){
		
		alert(err.message);
		console.log(err.message);
	}
}

function get_db() {
	return new sqlite3.Database(sqlitePath);
}

function init_cp_table(obj, callback1){
	var db = get_db();
	window.CP_TABLE_LIST = [];
	db.serialize(function() {
		db.all("select * from CP_TABLE order by id asc", function(err, tables){
			if (err) {
				console.log(err.message);
				return;
			};
			for(var i = 0; i < tables.length; i++) {
				var row = tables[i];
				obj[row.ID] = row.NUMBER;
				window.CP_TABLE_LIST.push(row.ID);
				window.CP_TABLE_LIST.sort();
			}
			callback1();
		});
		db.all("select count(id) as cp_num from CP_TREND", function(err, data){
			//console.log(JSON.stringify(data));
			window.CP_INDEX = (Number)(data[0]["cp_num"]) / 5 + 1;
		});
		db.all("select max(id), qs from CP_TREND", function(err, data){
			if(data[0]["QS"]) {
				var day = data[0]["QS"].substr(0 ,2);
				var now_day = new Date().format("dd");
				if(day === now_day) {
					window.CP_QS = (Number)(data[0]["QS"].substr(3)) + 1;
				} else {
					window.CP_QS = 1;
				}
			} else {
				window.CP_QS = 1;
			}
		});
	});
	
	db.close();
}

function add_cp_trend(args) {
	var db = get_db();
	var sql = "insert into CP_TREND (id, qs, win_num, table_index, figure, big_or_small, parity, compare) values (?,?,?,?,?,?,?,?)";
	db.serialize(function() {
		for(var i = 0; i < args.length; i++) {
			db.run(sql, args[i], function(err, d){
				log_err(err);
			});
		}
	});
	db.close();
}

function add_large_cp_trend(args_list, cb) {
	var db = get_db();
	var sql = "insert into CP_TREND (id, qs, win_num, table_index, figure, big_or_small, parity, compare) values (?,?,?,?,?,?,?,?)";
	db.serialize(function() {
		db.run("delete from CP_TREND", function(){
			for(var i = 0; i < args_list.length; i++) {
				for(var j = 0; j < args_list[i].length; j++) {
					db.run(sql, args_list[i][j], function(err, d){
						log_err(err);
					});
				}
			}
			cb();
		});
	});
	db.close();
}

function get_cp_trend_by_id(id, callback) {
	var db = get_db();
	db.all("select * from CP_TREND where id=?", [id], callback);
	db.close();
}


function get_cp_trend_by_figure(figure, show_num, callback) {
	var db = get_db();
	// db.all("select * from CP_TREND where figure=? order by table_index asc", ['' + figure], callback);
	db.all("select * from (select * from CP_TREND  where figure=? order by table_index desc limit ?) order by table_index asc", ['' + figure, show_num], callback);
	db.close();
}

function get_cp_trend_each_row_by_figure(figure, show_num, callback) {
	var db = get_db();
	// db.all("select * from CP_TREND where figure=? order by table_index asc", ['' + figure], callback);
	db.each("select * from (select * from CP_TREND  where figure=? order by table_index desc limit ?) order by table_index asc", ['' + figure, show_num], callback);
	db.close();
}

function get_last_cp_trend(callback) {
	var db = get_db();
	db.all("select * from CP_TREND where table_index=(select max(table_index) from CP_TREND)", callback);
	db.close();
}

function delete_cp_trend_by_table_index(table_index) {
	var db = get_db();
	db.run("delete from CP_TREND where table_index=?", [table_index]);
	db.close();
}

function delete_cp_trend_by_last_table_index() {
	var db = get_db();
	db.run("delete from CP_TREND where table_index=(select max(table_index) from CP_TREND)");
	db.close();
}

function get_last_ten_cp_trend(figure, callback) {
	var db = get_db();
	db.all("select * from CP_TREND where figure=? order by table_index desc limit 0, 9", [figure + ""], callback);
	db.close();
}

// function add_cp_trend(id, qs, win_num, table_index, len5, len6, len7, len8, len9, len10) {
// 	var db = get_db();
// 	db.run("insert into (id, qs, win_num, table_index, len5, len6, len7, len8, len9, len10) values (?,?,?,?,?,?,?,?,?,?)", [id, qs, win_num, table_index, len5, len6, len7, len8, len9, len10]);
// 	db.close();
// }
